#!/use/bin/python
# coding=utf-8
# 将 每日流失及开机频次开机率提升 输出到飞书
import redis
import datetime
import pymysql
import requests
import json
import pandas as pd
from tabulate import tabulate

from dbutils.pooled_db import PooledDB


logDetail = []

# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf', port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',port=3306)  # 1为连接池里的最少连接数
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql,conf=2):
    if conf == 1:
        conn, cur = getConfConnection()
    else:
        conn, cur = getDataConnection()
    #print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None :
        num = numResult[0]
    if num is None:
        return 0
    return num

# redis
def getReidsConnection():
    redis_info = {
        "host": "172.20.151.90",
        "password": "Coocaa2022",
        "port": 6379,
        "db": 8
    }

    r = redis.Redis(**redis_info, decode_responses=True)
    return r

# 主动服务各服务 订阅设备变化情况
#date 当天  serId 服务 execute_flag 是否执行一次 0否 1是
def insertSubscribeNumber(toDay,serId,execute_flag):
    try:
        conn, cur = getDataConnection()
        r = getReidsConnection()

        # 累计订阅设备
        onceKey = f'LOG_DEVICE_ACTIVE_ONCE_{serId.upper()}'
        # 累计订阅用户量
        onceKeyNum = r.scard(onceKey)
        #获取每天的总量
        cumulateNum = selectNumBySql(
            f"select cumulate_num from analysis_subscribe_number t where t.service_key ='{serId}' and t.ref_date =  '{toDay}' and t.execute_flag = {execute_flag}")
        #获取已经计算好的每天变化量
        newNum = selectNumBySql(
            f"select new_num from analysis_subscribe_number t where t.service_key ='{serId}' and t.ref_date =  '{toDay}' and t.execute_flag = {execute_flag}")
        # 获取已经计算好的净新增数量
        increaseNum = selectNumBySql(
            f"select increase_num from analysis_subscribe_number t where t.service_key ='{serId}' and t.ref_date =  '{toDay}' and t.execute_flag = {execute_flag}")
        # 获取已经计算好的流失数量
        reduceNum = selectNumBySql(
            f"select reduce_num from analysis_subscribe_number t where t.service_key ='{serId}' and t.ref_date =  '{toDay}' and t.execute_flag = {execute_flag}")
        serName = serIdToName(serId)
        if serId == 'zjkj':
            logDetail.append(f"{serName}:累计订阅用户量为{onceKeyNum},订阅总量为{cumulateNum},订阅设备量变化数为{newNum},净新增数为{increaseNum},流失数为{reduceNum}\n")
        else:
            logDetail.append(f"{serName}:订阅总量为{cumulateNum},订阅设备量变化数为{newNum},净新增数为{increaseNum},流失数为{reduceNum}\n")

    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

#获取 用户关闭早间服务的来源
def closeServer(toDay):
    conn, cur = getDataConnection()
    sql = f"""
        select count(1),JSON_EXTRACT(t.msg,'$.from') '来源' from log_common_service_{yyMMddDate} t where t.`key` = 'closeServer2' and
DATE_FORMAT(t.create_time, '%Y-%m-%d') ='{toDay}' and JSON_EXTRACT(t.msg,'$.serId') ='ZJKJ' group by  JSON_EXTRACT(t.msg,'$.from')
    """
    df = pd.read_sql(sql, con=conn)
    title = "\n用户关闭早间服务的来源\n"
    msg = tabulate(df, headers='keys', tablefmt='pretty')
    logDetail.append(title)
    logDetail.append(msg)

#获取 服务关闭挽留弹窗显示后用户选择
def retentionAlertResult(toDay):
    conn, cur = getDataConnection()
    sql = f"""
        select count(1),tt.choose '用户选择' from (
    select count(1),JSON_EXTRACT(t.msg,'$.choose') choose,t.active_id from log_common_service_{yyMMddDate} t where t.`key` = 'retentionAlertResult' and
    DATE_FORMAT(t.create_time, '%Y-%m-%d') ='{toDay}' group by JSON_EXTRACT(t.msg,'$.choose'),t.active_id)tt group by tt.choose
    """
    df = pd.read_sql(sql, con=conn)
    title = "\n早间服务关闭挽留弹窗显示后用户选择\n"
    msg = tabulate(df, headers='keys', tablefmt='pretty')
    logDetail.append(title)
    logDetail.append(msg)

def reduceDevice(toDay):
    reducekey = f"LOG_DEVICE_REDUCE_ACTIVE_ZJKJ_{toDay}"
    conn, cur = getConfConnection()
    r = getReidsConnection()
    reduceNun = r.scard(reducekey)
    activeIdSet = r.smembers(reducekey)
    # 计算每一个设备的的订阅时长
    if reduceNun > 0:
        whereStr = "and t.active_id in ("
        for activeId in activeIdSet:
            whereStr = whereStr + """'""" + activeId + """',"""
        whereStr = whereStr[:-1] + ")"
        selectDaySql01 = f"""
                     select count(1) '设备数', dayNum '累计订阅天数' from (
                       select TIMESTAMPDIFF(DAY,t.zjkj_open_time,'{toDay}') dayNum from life_assistant_devices t where t.zjkj_open_time is not null
                    {whereStr}) tt group by tt.dayNum order by dayNum desc
                       """
        df01 = pd.read_sql(selectDaySql01, con=conn)
        msg01 = tabulate(df01, headers='keys', tablefmt='pretty')
        title01 = "\n早间服务流失用户累计订阅天数\n"
        logDetail.append(title01)
        logDetail.append(msg01)

        selectDaySql02 = f"""                             								 
					select count(1) num,'是' as '是否执行过' from life_assistant_devices t where 1=1 {whereStr} and  (switches_state1 & (1 << (11 - 1)))<>0
					union all 
					select count(1) num,'否' as '是否执行过' from life_assistant_devices t where 1=1 {whereStr} and  (switches_state1 & (1 << (11 - 1)))=0
                               """
        df02 = pd.read_sql(selectDaySql02, con=conn)
        msg02 = tabulate(df02, headers='keys', tablefmt='pretty')
        title02 = "\n早间服务流失用户是否执行过服务\n"
        logDetail.append(title02)
        logDetail.append(msg02)


#根据serId 获取服务中文名
def serIdToName(serId):
    if serId =='zjkj':
        serName = "早间开机"
    elif serId =='jkzx':
        serName = "健康作息"
    elif serId =='yjyl':
        serName = "夜间音量"
    elif serId =='zjtx':
        serName = "追剧提醒"
    elif serId =='sjtx':
        serName = "睡觉提醒"
    elif serId =='zmgn':
        serName = "助眠功能"
    else:
        serName = "总体"
    return serName


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdateYMD(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    return re_date

#如 2022年12月13号 获取 221213 作为分表后缀
def getdateyyMMdd(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    re_date = re_date[2:]
    return re_date

# 执行各服务每日订阅次数
def serviceSubscribeNumber():
    logDetail.append(f"每日订阅情况:\n")
    insertSubscribeNumber(toDay, 'all', 1)
    insertSubscribeNumber(toDay, 'zjkj', 0)
    insertSubscribeNumber(toDay, 'yjyl', 1)
    insertSubscribeNumber(toDay, 'zjtx', 1)
    insertSubscribeNumber(toDay, 'sjtx', 0)
    insertSubscribeNumber(toDay, 'zmgn', 0)


def printFeiShu(msg):
    msg_content = "{env}\n{curTime}\n\n{msg}".format(env=env, curTime=toDay, msg="".join(msg))
    print(msg_content)

    payload = {
        "msg_type": "text",
        "content": {
            "text": msg_content
        }
    }

    headers = {
        'Content-Type': 'application/json'
    }

    data = json.dumps(payload)

    # 按照utf-8编码成字节码
    data = data.encode("utf-8")
    #测试机器人
    url = 'https://open.feishu.cn/open-apis/bot/v2/hook/4238d051-3d9a-438f-9614-eb324f2ea845'
    #正式机器人
    #url = "https://open.feishu.cn/open-apis/bot/v2/hook/4b102c84-338a-4bef-87f9-441ecd3f737b"
    response = requests.post(url, data=data, headers=headers)
    print(response.text)

if __name__ == '__main__':
    env = f"主动服务订阅用户数-每日净新增及流失用户数"
    d =1
    #today = todayYMD()
    toDay = getdate(d)
    toDayYMd = getdateYMD(d)
    yyMMddDate = getdateyyMMdd(d)
    #today = "2021-12-13"
    serviceSubscribeNumber()
    closeServer(toDay)
    retentionAlertResult(toDay)
    reduceDevice(toDayYMd)
    printFeiShu(logDetail)


